package com.crm.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.crm.entity.PO;
import com.crm.entity.QuotParts;
import com.crm.util.ApplicationContext;

public class PODAO {
	protected final Log logger = LogFactory.getLog(getClass());

	public  int insert(PO c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();
			return 0;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  int insertList(List<PO> lpo) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			for(PO p:lpo){
				session.saveOrUpdate(p);
			}
			tx.commit();
			return 0;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  int update(PO c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();
			return 0;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  int delete(PO c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.delete(c);
			tx.commit();
			return 0;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<PO> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<PO> custObjs=new ArrayList<PO>();
			 List objs=null;
			Query query=session.createQuery("from PO where business_id=:busid");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((PO)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  PO findById(int i) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			PO c=(PO)session.load(PO.class, i);
			return c;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  List<PO> getPurchaseOrder(int enqid,int suid,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From PO  where enq_id=:enqid and supp_id=:suid and business_id=:busid order by part_id";
			Query qry=session.createQuery(qtr);
			qry.setInteger("enqid", enqid);
			qry.setInteger("suid", suid);
			qry.setInteger("busid", busid);
			List<PO> qresult=qry.list();
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}

	}
	public  List<Integer> getSupp(int enqid,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="select  distinct p.supp_id from purchaseorder p where p.enq_id=:enqid and p.business_id=:busid";
			Query qry=session.createSQLQuery(qtr);
			qry.setInteger("enqid", enqid);
			qry.setInteger("busid", busid);
			List<Integer> qresult=qry.list();
			System.out.println("the supplier list is"+qresult);
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<PO> getPOByEnqidBusid(int enqid,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="from PO where enq_id=:enqid and business_id=:busid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("enqid", enqid);
			qry.setInteger("busid", busid);
			List<PO> qresult=(List<PO>)qry.list();
			System.out.println("the supplier list is"+qresult);
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  void deletelastPO(int enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			String qry="Delete from PO where enq_id=:eqid and business_id=:busid";
			Query query=session.createQuery(qry);
			query.setInteger("eqid", enqid);
			query.setInteger("busid",busid);
			query.executeUpdate();
			tx.commit();
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public PO findbyEnqIdPartIdBusid(int enqid,int partid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			String qry="from PO where enq_id=:enqid and part_id=:partid and business_id=:busid";
			Query query=session.createQuery(qry);
			query.setInteger("enqid", enqid);
			query.setInteger("busid",busid);
			query.setInteger("partid",partid);
			PO po=(PO)query.uniqueResult();
			return po;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
}
